﻿using System;
using System.Configuration;
using System.Data;
using System.IO;
using Icms.Core;
using Icms.Core.Service;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace Icms.Service
{
    public class ReportBuilder : IReportBuilder
    {
        public string BuildReport(DataSet data, string templateName, DateTime dateFrom, DateTime dateTo, string title)
        {
            const int offset = 10;
            var fileName = templateName + Guid.NewGuid();
            var target = ConfigurationManager.AppSettings["storagePath"] + @"\RequestedReports\" + fileName + ".xls";
            var template = new FileInfo(ConfigurationManager.AppSettings["storagePath"] + @"\ReportTemplates\" + templateName + ".xls");

            using (var fs = new FileStream(template.FullName, FileMode.Open))
            {
                var templateWorkbook = new HSSFWorkbook(fs, true);

                var style1 = templateWorkbook.CreateCellStyle();
                style1.BorderBottom = CellBorderType.THIN;
                style1.BorderLeft = CellBorderType.THIN;
                style1.BorderTop = CellBorderType.THIN;
                style1.BorderRight = CellBorderType.THIN;

                var sheet = templateWorkbook.GetSheetAt(0);
                var t = data.Tables[0];

                sheet.GetRow(3).GetCell(2).SetCellValue(title);
                sheet.GetRow(4).GetCell(2).SetCellValue(string.Format("{0:dd.MM.yyyy} - {1:dd.MM.yyyy}", dateFrom, dateTo));
                sheet.GetRow(5).GetCell(2).SetCellValue(DateTime.Now.ToShortDateString());

                for (var i = 0; i < t.Rows.Count; i++)
                {
                    var row = sheet.CreateRow(i + offset);
                    var cell = row.CreateCell(0);
                    cell.SetCellValue(i + 1);
                    cell.CellStyle = style1;

                    var cell1 = row.CreateCell(1);
                    cell1.CellStyle = style1;
                    cell1.SetCellValue((t.Rows[i][0].IfValIsThen(DBNull.Value, string.Empty)).ToString());

                    for (var ni = 2; ni < t.Columns.Count + 1; ni++)
                    {
                        var c = row.CreateCell(ni);
                        c.CellStyle = style1;
                        var v = t.Rows[i][ni - 1].IfValIsThen(DBNull.Value, 0);
                        v = v.IfValIsThen("", 0);

                        double dbv;
                        if (double.TryParse(v.ToString(), out dbv))
                            c.SetCellValue(dbv);
                        else
                            c.SetCellValue(v.ToString());
                    }
                }

                var trow = sheet.CreateRow(t.Rows.Count + offset);
                var totalSCell = trow.CreateCell(1);
                totalSCell.SetCellValue("Total");
                totalSCell.CellStyle = style1;

                //create totals from data column nr 1))
                for (var col = 1; col < t.Columns.Count; col++)
                {
                    double total = 0;
                    //get total
                    for (var row = 0; row < t.Rows.Count; row++)
                    {
                        var sv = t.Rows[row][col].IfValIsThen(DBNull.Value, 0).ToString();
                        double r;
                        double.TryParse(sv, out r);
                        total += r;
                    }
                    var tcell = trow.CreateCell(col + 1);
                    tcell.CellStyle = style1;
                    tcell.SetCellValue(total);
                }


                var ms = new MemoryStream();
                templateWorkbook.Write(ms);

                var outStream = File.Open(target, FileMode.CreateNew);
                ms.WriteTo(outStream);
                outStream.Flush();
                outStream.Close();
            }

            return fileName;
        }
    }
}